const connection = require('../app/database')

class UserService {
	async create(user) {
		const { name, password, nickname, email, phone, age, avatar_url } = user
		console.log(name, password, nickname, email, phone, age)
		const userStatement = `INSERT INTO users (name, password_hash, role_id) VALUES (?, ?, 0);`
		const [userResult] = await connection.execute(userStatement, [
			name,
			password
		])
		console.log(`output->123`, 456)
		// 4.检查是否提供了昵称，如果没有，则生成一个默认昵称
		let effectiveNickname = nickname || 'wx_' + name
		// 5.1 准备插入用户信息数据的字段和值
		let profileFields = ['user_id', 'nickname']
		let profileValues = [userResult.insertId, effectiveNickname]
		let profilePlaceholders = ['?', '?'] // 初始化占位符

		// 5.2 根据提供的值动态构建 SQL 语句
		if (email) {
			profileFields.push('email')
			profileValues.push(email)
			profilePlaceholders.push('?')
		}
		if (phone) {
			profileFields.push('contact_info')
			profileValues.push(phone)
			profilePlaceholders.push('?')
		}
		if (age) {
			profileFields.push('age')
			profileValues.push(age)
			profilePlaceholders.push('?')
		}
		if(avatar_url) {
			profileFields.push('avatar_url')
			profileValues.push(avatar_url)
			profilePlaceholders.push('?')
		}

		// 5.3 构建最终的 SQL 语句
		const profileStatement = `
			INSERT INTO user_profiles (${profileFields.join(', ')})
			VALUES (${profilePlaceholders.join(', ')})
		`

		// 5.4 执行用户信息插入
		const [profileResult] = await connection.execute(
			profileStatement,
			profileValues
		)
		return profileResult
	}
	async findUserByName(name) {
		const statement = `SELECT * FROM users WHERE name = ? AND status = 0`
		const [values] = await connection.execute(statement, [name])
		return values
	}

	async findUserByNameAndEmail(name, email) {
		const statement = `
			SELECT u.id,u.name
			FROM users u
			LEFT JOIN user_profiles up
			ON u.id = up.user_id
			WHERE u.name = ?
			AND status = 0
			AND	up.email = ?
		`
		const [values] = await connection.execute(statement, [name, email])
		return values
	}


	// 获取用户角色信息
	async queryUserInfo(id) {
		const statement = `	
		SELECT 
			u.name name,
			up.user_id,
			up.avatar_url,
			up.bio,
			up.age,
			up.contact_info,
			up.email,
			up.nickname,
			JSON_OBJECT('id', r.id, 'name', r.name, 'description', r.description, 'created_at', r.created_at, 'updated_at', r.updated_at) AS role
		FROM 
    	user_profiles up
		JOIN 
    	users u ON up.user_id = u.id
		JOIN 
    	roles r ON u.role_id = r.id
		WHERE 
    	up.user_id = ?
		`
		const [values] = await connection.execute(statement, [id])
		return values[0]
	}

	// 获取菜单信息
	async queryMenusList(roleId) {
		const statement = `
		SELECT 
    	m1.id AS id,
    	m1.name AS name,
    	m1.url AS url,
    	m1.icon AS icon,
    	JSON_ARRAYAGG(
        JSON_OBJECT(
					'id', m2.id, 'name', m2.name, 'url', m2.url, 'icon', m2.icon) 
    		) AS children
		FROM 
    	menus m1
		LEFT JOIN 
    	menus m2 ON m2.parent_id = m1.id
		JOIN 
    	role_menus rm ON rm.menu_id = m1.id
		WHERE 
    	rm.role_id = ?
    AND 
			m2.id IS NOT NULL 
		GROUP BY 
    	m1.id
		ORDER BY
			m1.id, m2.id;
		`
		const [values] = await connection.execute(statement, [roleId])
		return values
	}

	// 获取全部用户信息
	async queryUsersInfo({
		name,
		age,
		nickname,
		phone,
		startDate,
		endDate,
		status = 0,
		email,
		limit = 10,
		offset = 0
	}) {
		// 查询总记录数，包括筛选条件
		let countQuery = `
    SELECT
        COUNT(*) as count
    FROM
        users u
    LEFT JOIN
        user_profiles up ON u.id = up.user_id
    `

		// 基础查询，用于获取用户信息和分页
		let baseQuery = `
    SELECT
        u.id id,
        u.name name,
        u.created_at created_at,
        u.updated_at updated_at,
        u.status status,
        up.bio bio,
        up.avatar_url avatar_url,
        up.age age,
        up.contact_info phone,
        up.email email,
        up.nickname nickname
    FROM
        users u
    LEFT JOIN
        user_profiles up ON u.id = up.user_id
    `

		// 存储查询条件的数组
		let conditions = []
		// 存储对应值的数组
		let params = []
		let countParams = [] // 可能和 params 有所不同，用于 countQuery

		// 添加查询条件到数组中
		if (name) {
			conditions.push('u.name LIKE ?')
			params.push(`%${name}%`)
			countParams.push(`%${name}%`)
		}

		if (age) {
			conditions.push('up.age = ?')
			params.push(age)
			countParams.push(age)
		}
		if (status) {
			conditions.push('u.status = ?')
			params.push(status)
			countParams.push(status)
		}
		if (nickname) {
			conditions.push('up.nickname LIKE ?')
			params.push(`%${nickname}%`)
			countParams.push(`%${nickname}%`)
		}

		if (phone) {
			conditions.push('up.contact_info = ?')
			params.push(phone)
			countParams.push(phone)
		}
		if (email) {
			conditions.push('up.email = ?')
			params.push(email)
			countParams.push(email)
		}

		if (startDate && endDate) {
			conditions.push('u.created_at BETWEEN ? AND ?')
			params.push(startDate, endDate)
			countParams.push(startDate, endDate)
		}

		// 如果存在查询条件，则将其添加到基础查询中
		if (conditions.length) {
			const whereClause = ' WHERE ' + conditions.join(' AND ')
			baseQuery += whereClause
			countQuery += whereClause
		}

		// baseQuery += ' LIMIT ? OFFSET ?'
		// params.push(parseInt(limit), parseInt(offset))

		// 先执行计数查询，获取总记录数
		const [countResults] = await connection.execute(countQuery, countParams)
		const total = countResults[0].count

		// 然后用相同的查询条件执行基础查询，获取分页结果
		const [results] = await connection.execute(baseQuery, params)

		// 返回包含总记录数、分页信息和查询结果的对象
		return {
			totalCount: total,
			list: results.slice(parseInt(offset), parseInt(limit) + parseInt(offset))
		}
	}

	// 更新用户信息
	async updateUsersInfo(
		{ avatar_url, bio, age, phone, email, nickname },
		userId
	) {
		// 初始 SQL 语句中包含 SET 关键字，但没有任何更新字段
		let statement = 'UPDATE user_profiles SET'
		let parameters = []

		// 定义一个辅助函数来追加 SQL 语句和参数
		function addField(field, value) {
			if (value !== undefined && value !== null) {
				// 如果字段是第一个添加的字段，不需要逗号，否则需要
				statement += parameters.length ? ', ' : ' '
				statement += `${field} = ?`
				parameters.push(value)
			}
		}

		// 对每个字段调用 addField 来构建语句和参数
		addField('avatar_url', avatar_url)
		addField('bio', bio)
		addField('age', age)
		addField('contact_info', phone)
		addField('email', email)
		addField('nickname', nickname)
		// 在最后添加 WHERE 子句
		statement += ' WHERE user_id = ?'
		parameters.push(userId)

		// 如果没有字段需要更新，则直接返回
		if (parameters.length === 1) {
			console.log('No fields to update')
			return
		}

		console.log(statement) // 输出最终的 SQL 语句进行调试
		console.log(parameters) // 输出对应的参数数组进行调试

		// 执行 SQL 语句
		const [values] = await connection.execute(statement, parameters)
		return values
	}

	// 删除用户
	async deleteUser(userid) {
		const queryState = `
			DELETE FROM
				user_profiles 
			WHERE
			 	user_id = ?
		`
		await connection.execute(queryState, [userid])
		const statement = `
			DELETE FROM
				users 
			WHERE
			 	id = ?
		`
		const [values] = await connection.execute(statement, [userid])
		return values
	}
	// async 修改用户角色级别
	async editRoleLevel(user_id, role) {
		const statement = `UPDATE users SET role_id = ? WHERE id = ?`
		const [res] = await connection.execute(statement, [role, user_id])
		return res
	}
	// 修改密码
	async updatePassword(name, password) {
		const statement = `UPDATE users SET password_hash = ? WHERE name = ?`
		const [res] = await connection.execute(statement, [password, name])
		return res
	}
}
module.exports = new UserService()
